<?php 
	$txtFIni=$_POST['txtDesde'];
	$txtFFin = $_POST['txtHasta'];
	$cmb_Proveedor	= $_POST['txtProveedor'];
	header('Content-type: application/vnd.ms-excel');
	header("Content-Disposition: attachment; filename=LibroFacturasPagar.xls");
	header("Pragma: no-cache");
	header("Expires: 0");
	require_once("../conexion/conexion.php");
	require_once("../Funciones/funciones_publicas.php");
	$fun = new funciones_publicas();
	$mes = $fun->SeleccionaMes($cmb_Mes);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Documento sin t&iacute;tulo</title>
</head>

<body>
<?php
extract($_POST);
	//cliente
 	if ($cmb_Proveedor=='0')
	{
		$cmb_Proveedor="null";
	}
	
	//fecha desde
	if ($txtFIni=='')
	{
		$txtFIni="null";
	}
	
	//fecha hasta
	if ($txtFFin=='')
	{
		$txtFFin="null";
	}

//factura normal
$con = new Conexion();
$con2 = $con->conexionBD("fpproduccion");
$sql = "select 
		date_format(FacNFecha,'%d/%m/%Y') as fecha,
		FacNNum,
		ProvNombre,
		facturanormal.ProvRut,
		FacNNeto,
		FacNIVA,
		FacNTotal,
		FacNImpEsp,
		date_format(FacNFechaVenc,'%d/%m/%Y') as fechavenc
		
		from facturanormal
		inner join proveedor on
		proveedor.ProvRut=facturanormal.ProvRut
		where 
		(('$cmb_Proveedor' is NULL) or (facturanormal.ProvRut = '".$cmb_Proveedor."')) and
		((($txtFIni is NULL) and ($txtFFin is NULL)) or DATE(FacNFecha) BETWEEN '".$txtFIni."' and '".$txtFFin."') and
		FacNEstado='PENDIENTE'
		order by FacNFecha asc";

//		(($cmb_Mes is NULL) or (month(FacNFecha) = '".$cmb_Mes."')) and
//		(($cmb_Agno is NULL) or (year(FacNFecha) = '".$cmb_Agno."')) and
		
$resultado = mysql_query($sql) or die ($con->error_mysql("Error en la consulta".mysql_error()));
$fila = mysql_num_rows($resultado);
$con->cerrarBD();
//factura exenta
$con = new Conexion();
$con2 = $con->conexionBD("fpproduccion");
$sql = "select 
		date_format(FacEFecha,'%d/%m/%Y') as fecha,
		FacENum,
		ProvNombre,
		facturaexenta.ProvRut,
		FacETotal,
		date_format(FacEFechaVenc,'%d/%m/%Y') as fechavenc
		
		from facturaexenta
		inner join proveedor on
		proveedor.ProvRut=facturaexenta.ProvRut
		where 
		(('$cmb_Proveedor' is NULL) or (facturaexenta.ProvRut = '".$cmb_Proveedor."')) and
		((($txtFIni is NULL) and ($txtFFin is NULL)) or DATE(FacEFecha) BETWEEN '".$txtFIni."' and '".$txtFFin."') and
		(FacEEstado='PENDIENTE')
		order by FacEFecha asc";

//		(($cmb_Mes is NULL) or (month(FacEFecha) = '".$cmb_Mes."')) and
//		(($cmb_Agno is NULL) or (year(FacEFecha) = '".$cmb_Agno."')) and
		
$resultado2 = mysql_query($sql) or die ($con->error_mysql("Error en la consulta".mysql_error()));
$fila2 = mysql_num_rows($resultado2);
$con->cerrarBD();
//factura electronica
$con = new Conexion();
$con2 = $con->conexionBD("fpproduccion");
$sql = "select 
		date_format(FacElecFecha,'%d/%m/%Y') as fecha,
		FacElecNum,
		ProvNombre,
		facturaelectronica.ProvRut,
		FacElecNeto,
		FacElecIVA,
		FacElecTotal,
		date_format(FacElecFechaVenc,'%d/%m/%Y') as fechavenc
		
		from facturaelectronica
		inner join proveedor on
		proveedor.ProvRut=facturaelectronica.ProvRut
		where 
		(('$cmb_Proveedor' is NULL) or (facturaelectronica.ProvRut = '".$cmb_Proveedor."')) and
		((($txtFIni is NULL) and ($txtFFin is NULL)) or DATE(FacElecFecha) BETWEEN '".$txtFIni."' and '".$txtFFin."') and
		(FacElecEstado='PENDIENTE')
		order by FacElecFecha asc";

//		(($cmb_Mes is NULL) or (month(FacElecFecha) = '".$cmb_Mes."')) and
//		(($cmb_Agno is NULL) or (year(FacElecFecha) = '".$cmb_Agno."')) and
		
$resultado3 = mysql_query($sql) or die ($con->error_mysql("Error en la consulta".mysql_error()));
$fila3 = mysql_num_rows($resultado3);
$con->cerrarBD();

//factura compra
$con = new Conexion();
$con2 = $con->conexionBD("fpproduccion");
$sql = "select 
		date_format(FCFecha,'%d/%m/%Y') as fecha,
		FCNum,
		ProvNombre,
		fc.ProvRut,
		FCNeto
		
		from faccompra fc
		inner join proveedor p on
		p.ProvRut=fc.ProvRut
		where
		(('$cmb_Proveedor' is NULL) or (fc.ProvRut = '".$cmb_Proveedor."')) and
		((($txtFIni is NULL) and ($txtFFin is NULL)) or DATE(FCFecha) BETWEEN '".$txtFIni."' and '".$txtFFin."') and
		(FCEstado='PENDIENTE')
		order by FCFecha asc";

//		(($cmb_Mes is NULL) or (month(FCFecha) = '".$cmb_Mes."')) and
//		(($cmb_Agno is NULL) or (year(FCFecha) = '".$cmb_Agno."')) and

$resultado4 = mysql_query($sql) or die ($con->error_mysql("Error en la consulta".mysql_error()));
$fila4 = mysql_num_rows($resultado4);
$con->cerrarBD();

if(($fila >0)or($fila2>0)or($fila3>0)or($fila4>0))
{
?>
<table width="135%" border="1" cellpadding="0" cellspacing="0" >
<tr>
<td colspan="10" bgcolor="#CCCCCC"><div align="center" style="font-size:24px"><strong>Libro de Facturas por Pagar.</strong></div></td>
</tr>
<tr>
<td colspan="10">&nbsp;</td>
</tr>
<tr>
	<th width="12%" bgcolor="#CCCCCC">F.EMISION</th>
	<th width="12%" bgcolor="#CCCCCC">F.VENC</th>
	<th width="12%" bgcolor="#CCCCCC">FACTURA</th>
	<th width="13%" bgcolor="#CCCCCC">TIPO</th>
	<th width="18%" bgcolor="#CCCCCC">PROVEEDOR</th>
	<th width="12%" bgcolor="#CCCCCC">RUT</th>
	<th width="8%" bgcolor="#CCCCCC">IMP.ESP</th>
	<th width="8%" bgcolor="#CCCCCC">NETO</th>
	<th width="8%" bgcolor="#CCCCCC">IVA</th>
	<th width="9%" bgcolor="#CCCCCC">TOTAL</th>
</tr>
<?php
$sumatoria = 0;
$sumaIVA = 0;
$sumaNeto = 0;
$sumaImpEsp = 0;
//factura normal
while($reg = mysql_fetch_array($resultado))
{
	echo "<tr>";
	echo "<td width=\"12%\">".$reg['fecha']."</td>";
	echo "<td width=\"12%\">".$reg['fechavenc']."</td>";
	echo "<td width=\"12%\">".$reg['FacNNum']."</td>";
	echo "<td width=\"13%\">NORMAL</td>";
	echo "<td width=\"18%\">".$reg['ProvNombre']."</td>";
	echo "<td width=\"12%\">".$reg['ProvRut']."</td>";
	echo "<td width=\"8%\">".$reg['FacNImpEsp']."</td>";
	echo "<td width=\"8%\">".number_format ($reg['FacNNeto'],0,',','.')."</td>";
	echo "<td width=\"8%\">".number_format ($reg['FacNIVA'],0,',','.')."</td>";
	echo "<td width=\"9%\">".number_format ($reg['FacNTotal'],0,',','.')."</td>";
	echo "</tr>";
	$sumatoria += $reg['FacNTotal'];
	$sumaIVA += $reg['FacNIVA'];
	$sumaNeto += $reg['FacNNeto'];
	$sumaImpEsp += $reg['FacNImpEsp'];
}
//factura exenta
while($reg = mysql_fetch_array($resultado2))
{
	echo "<tr>";
	echo "<td width=\"12%\">".$reg['fecha']."</td>";
	echo "<td width=\"12%\">".$reg['fechavenc']."</td>";
	echo "<td width=\"12%\">".$reg['FacENum']."</td>";
	echo "<td width=\"13%\">EXENTA</td>";
	echo "<td width=\"18%\">".$reg['ProvNombre']."</td>";
	echo "<td width=\"12%\">".$reg['ProvRut']."</td>";
	echo "<td width=\"8%\">0</td>";
	echo "<td width=\"8%\">0</td>";
	echo "<td width=\"8%\">0</td>";
	echo "<td width=\"9%\">".number_format ($reg['FacETotal'],0,',','.')."</td>";
	echo "</tr>";
	$sumatoria += $reg['FacETotal'];
}
//factura electronica
while($reg = mysql_fetch_array($resultado3))
{
	echo "<tr>";
	echo "<td width=\"12%\">".$reg['fecha']."</td>";
	echo "<td width=\"12%\">".$reg['fechavenc']."</td>";
	echo "<td width=\"12%\">".$reg['FacElecNum']."</td>";
	echo "<td width=\"13%\">ELECTRONICA</td>";
	echo "<td width=\"18%\">".$reg['ProvNombre']."</td>";
	echo "<td width=\"12%\">".$reg['ProvRut']."</td>";
	echo "<td width=\"8%\">0</td>";
	echo "<td width=\"8%\">".number_format ($reg['FacElecNeto'],0,',','.')."</td>";
	echo "<td width=\"8%\">".number_format ($reg['FacElecIVA'],0,',','.')."</td>";
	echo "<td width=\"9%\">".number_format ($reg['FacElecTotal'],0,',','.')."</td>";
	echo "</tr>";
	$sumatoria += $reg['FacElecTotal'];
	$sumaIVA += $reg['FacElecIVA'];
	$sumaNeto += $reg['FacElecNeto'];
}
//factura compra
while($reg = mysql_fetch_array($resultado4))
{
	$iva = round($reg['FCNeto']*0.19);
	$total = $iva+$reg['FCNeto'];
	echo "<tr>";
	echo "<td width=\"12%\" >".$reg['fecha']."</td>";
	echo "<td width=\"12%\">&nbsp;</td>";
	echo "<td width=\"12%\" >".$reg['FCNum']."</td>";
	echo "<td width=\"13%\">FAC. COMPRA</td>";
	echo "<td width=\"18%\" >".$reg['ProvNombre']."</td>";
	echo "<td width=\"12%\" >".$reg['ProvRut']."</td>";
	echo "<td width=\"8%\" >0</td>";
	echo "<td width=\"8%\" >".number_format ($reg['FCNeto'],0,',','.')."</td>";
	echo "<td width=\"8%\" >".number_format ($iva,0,',','.')."</td>";
	echo "<td width=\"9%\" >".number_format ($total,0,',','.')."</td>";
	echo "</tr>";
	$sumatoria += $total;
	$sumaIVA += $iva;
	$sumaNeto += $reg['FCNeto'];
}
?>
<tr>
	<td colspan="6" style="text-align:right;"><strong>TOTAL</strong></td>
	<td><strong><?php echo number_format ($sumaImpEsp,0,',','.')?></strong></td>
	<td><strong><?php echo number_format ($sumaNeto,0,',','.')?></strong></td>
	<td><strong><?php echo number_format ($sumaIVA,0,',','.')?></strong></td>
	<td><strong><?php echo number_format ($sumatoria,0,',','.')?></strong></td>
</tr>
</table>
<?php 
}
?>
</body>
</html>
